package com.fsu.database.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Singleton Data Access Object (DAO) for Orders. This class should never be
 * used explicitly, but interfaced through the DatabaseManager 
 * class, instead.
 * @author Bonazza
 *
 */
public final class OrderDAO {
	
	/**
	 * Singleton instance object
	 */
	private static OrderDAO instance;
	
	/**
	 * SQL Database connection object.
	 */
	private  Connection con;
	
	/**
	 * Ctor
	 * @param c
	 */
	private OrderDAO(Connection c) {
		con = c;
	}
	
	/**
	 * Retrieve the singleton OrderDAO instance
	 * @param c
	 * @return The singleton instance of OrderDAO
	 */
	public synchronized static OrderDAO getInstance(Connection c) {
		if(instance == null)
			instance = new OrderDAO(c);
		
		return instance;
	}
	
	public List<Order> getOrders() throws SQLException {
		List<Order> orders = new ArrayList<Order>();
		
		List<Book> books = DatabaseManager.getBooks(null);
		List<DVD> dvds = DatabaseManager.getDvds(null);
		
		final String q = "SELECT o.order_id, d.Timestamp, d.user_login FROM Orders_Contains o, Ordered d";
		final String orderq = "SELECT o.Media_Title, o.Media_Year FROM Orders_Contains o WHERE o.order_id = ?";
		
		Statement stmt = con.createStatement();
		ResultSet cursor = stmt.executeQuery(q);
		
		while(cursor.next()) {
			long id = cursor.getLong(1);
			long timestamp = cursor.getLong(2);
			User user = DatabaseManager.getUser(cursor.getString(3));
			PreparedStatement pstmt = con.prepareStatement(orderq);
			pstmt.setLong(1, id);
			ResultSet c2 = pstmt.executeQuery();
			
			List<Media> media = new ArrayList<Media>();
			while(c2.next()) {
				String title = c2.getString(1);
				int year = c2.getInt(2);
				for (Book book : books) {
					if (book.getTitle().equals(title) && book.getYear() == year) {
						media.add(book);
					}
				}
				
				for (DVD dvd : dvds) {
					if (dvd.getTitle().equals(title) && dvd.getYear() == year) {
						media.add(dvd);
					}
				}	
			}
			
			orders.add(new Order(id, user, media, timestamp));
		}
		
		return orders;
	}
}
